package com.example.office.util.office;

import com.example.office.util.ObjectHelper;
import net.sf.jxls.transformer.XLSTransformer;
import org.apache.commons.jexl2.Expression;
import org.apache.commons.jexl2.JexlContext;
import org.apache.commons.jexl2.JexlEngine;
import org.apache.commons.jexl2.MapContext;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


/**
 * excel导出工具类
 *
 * @author jjh
 */
public class XNDBExcelUtil {
    private static Logger logger = LoggerFactory.getLogger(XNDBExcelUtil.class);
    /**
     * list集合每页最大数据个数
     * 用于exportByCommonTemplate,exportBySpecialTemplate
     */
    private static Integer templatePageSize = null;
    /**
     * list集合每页最大数据个数
     * 用于exportNoNeedTemplate
     */
    private static Integer pageSize = null;

    private static JexlEngine jexl = null;

    static {
        jexl = new JexlEngine();
        pageSize = 100000;
        templatePageSize = 5000;
    }

    /**
     * 通用导出(适用海量数据导出,更高效,不支持有参写法)  无需任何模板
     *
     * @param request
     * @param response
     * @param mainTitle 文件名 如:  测试
     * @param mapper    格式	如: {beanName.getMethod1().getMethod2()}{beanName.getMethod3()}=创建时间,{beanName.getMethod4()}=更新时间
     * @param beanName  mapper中必须包含该值
     * @param list      数据集合
     * @throws Exception
     */
    @SuppressWarnings("unchecked")
    public static <T> void exportNoNeedTemplate(HttpServletRequest request, HttpServletResponse response,
                                                String mainTitle, String mapper, String beanName, List<T> list) throws Exception {
        Map<String, Object> param = getExportDataMap(mainTitle, mapper, beanName, list, true);
        List<List<Object>> contentList = (List<List<Object>>) param.get("contentList");
        List<String> titleName = (List<String>) param.get("titleName");

        Workbook wb = new SXSSFWorkbook(10000);
        Font titleFont = wb.createFont();
        titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        CellStyle titleCellStyle = wb.createCellStyle();
        titleCellStyle.setFont(titleFont);
        titleCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        titleCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        titleCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        titleCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        titleCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        titleCellStyle.setBorderRight(CellStyle.BORDER_THIN);

        CellStyle contentCellStyle = wb.createCellStyle();
        contentCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        contentCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        contentCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        contentCellStyle.setBorderRight(CellStyle.BORDER_THIN);
        int pageIndex = 1;
        for (int i = 0; i < contentList.size(); i = pageSize + i) {
            List<List<Object>> sheetContentList = contentList.subList(i,
                    i + pageSize > contentList.size() ? contentList.size() : i + pageSize);
            Sheet sh = wb.createSheet("第" + pageIndex + "页");
            Row titileRow = sh.createRow(0);
            titileRow.setHeight((short) 400);
            for (int j = 0; j < titleName.size(); j++) {
                //sh.setColumnWidth(j, 30*256);
                sh.setColumnWidth(j, 30 * 156);
                Cell titleCell = titileRow.createCell(j);
                titleCell.setCellStyle(titleCellStyle);
                titleCell.setCellValue(titleName.get(j));
            }
            for (int j = 0; j < sheetContentList.size(); j++) {
                Row row = sh.createRow(j + 1);
                row.setHeight((short) 400);
                List<Object> rowContentList = sheetContentList.get(j);
                for (int k = 0; k < rowContentList.size(); k++) {
                    Cell cell = row.createCell(k);
                    cell.setCellStyle(contentCellStyle);
                    cell.setCellValue(rowContentList.get(k) == null ? "" : rowContentList.get(k).toString());
                }
            }
            pageIndex = pageIndex + 1;
        }

        String filename = charEncoding(request, mainTitle + ".xlsx");
        response.setContentType("application/x-msdownload;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + filename);
        wb.write(response.getOutputStream());
        response.getOutputStream().flush();
    }

    /**
     * 通用导出(适用海量数据导出,更高效,不支持有参写法)  无需任何模板 (非web环境测试用)
     *
     * @param mainTitle 文件名 如:  测试
     * @param mapper    格式	如: {beanName.getMethod1().getMethod2()}{beanName.getMethod3()}=创建时间,{beanName.getMethod4()}=更新时间
     * @param beanName  mapper中必须包含该值
     * @param list      数据集合
     * @throws Exception
     */
    @SuppressWarnings("unchecked")
    public static <T> void exportNoNeedTemplate(String mainTitle, String mapper, String beanName, List<T> list)
            throws Exception {
        Map<String, Object> param = getExportDataMap(mainTitle, mapper, beanName, list, true);
        List<List<Object>> contentList = (List<List<Object>>) param.get("contentList");
        List<String> titleName = (List<String>) param.get("titleName");

        Workbook wb = new SXSSFWorkbook(10000);
        Font titleFont = wb.createFont();
        titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        CellStyle titleCellStyle = wb.createCellStyle();
        titleCellStyle.setFont(titleFont);
        titleCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        titleCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        titleCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        titleCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        titleCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        titleCellStyle.setBorderRight(CellStyle.BORDER_THIN);

        CellStyle contentCellStyle = wb.createCellStyle();
        contentCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        contentCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        contentCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        contentCellStyle.setBorderRight(CellStyle.BORDER_THIN);
        int pageIndex = 1;
        for (int i = 0; i < contentList.size(); i = pageSize + i) {
            List<List<Object>> sheetContentList = contentList.subList(i,
                    i + pageSize > contentList.size() ? contentList.size() : i + pageSize);
            Sheet sh = wb.createSheet("第" + pageIndex + "页");
            Row titileRow = sh.createRow(0);
            titileRow.setHeight((short) 400);
            for (int j = 0; j < titleName.size(); j++) {
                //sh.setColumnWidth(j, 30*256);
                sh.setColumnWidth(j, 30 * 156);
                Cell titleCell = titileRow.createCell(j);
                titleCell.setCellStyle(titleCellStyle);
                titleCell.setCellValue(titleName.get(j));
            }
            for (int j = 0; j < sheetContentList.size(); j++) {
                Row row = sh.createRow(j + 1);
                row.setHeight((short) 400);
                List<Object> rowContentList = sheetContentList.get(j);
                for (int k = 0; k < rowContentList.size(); k++) {
                    Cell cell = row.createCell(k);
                    cell.setCellStyle(contentCellStyle);
                    cell.setCellValue(rowContentList.get(k) == null ? "" : rowContentList.get(k).toString());
                }
            }
            pageIndex = pageIndex + 1;
        }

        File tempTargetFile = new File("d:" + File.separator + mainTitle + ".xls");
        FileOutputStream fos = new FileOutputStream(tempTargetFile);
        wb.write(fos);
    }

    /**
     * 通用导出(适用少量数据导出)  无需另外配置模板文件 通用 exportTemplate.xlsx
     * @param request
     * @param response
     * @param mainTitle  文件名 如:  测试
     * @param mapper
     * 		格式	如: {beanName.getMethod1().getMethod2()}{beanName.getMethod3(param1,param2)}=创建时间,{beanName.getMethod4()}=更新时间
     * @param beanName   mapper中必须包含该值
     * @param list    数据集合
     * @throws Exception
     */
    //	public static <T> void exportByCommonTemplate(HttpServletRequest request,HttpServletResponse response,
    //			String mainTitle,String mapper,String beanName,List<T> list)
    //			throws Exception{
    //		excuteExportByTemplate(request, response, CacheUtil.getProperty("export.root")+File.separator+"exportTemplate.xlsx",
    //				mainTitle + ".xlsx", getExportDataMap(mainTitle, mapper, beanName, list,false));
    //	}
    //

    /**
     * 根据特殊模板导出(适用少量数据导出)
     *
     * @param request
     * @param response
     * @param templateName
     * @param outFileName
     * @param param
     * @throws Exception
     */
    public static void exportBySpecialTemplate(HttpServletRequest request, HttpServletResponse response,
                                               String templateName, String outFileName, Map<String, Object> param) throws Exception {
        excuteExportByTemplate(request, response, templateName, outFileName, param);
    }

    /**
     * 执行导出操作
     *
     * @param request
     * @param response
     * @param templateName
     * @param outFileName
     * @param param
     * @throws Exception
     */
    @SuppressWarnings("unchecked")
    private static void excuteExportByTemplate(HttpServletRequest request, HttpServletResponse response,
                                               String templateName, String outFileName, Map<String, Object> param) throws Exception {
        logger.info("=============模板导出开始   模板路径:{}  =================", templateName);

        String filename = charEncoding(request, outFileName);

        List<List<Object>> objects = new ArrayList<List<Object>>();
        List<String> newSheetNames = new ArrayList<String>();
        String beanName = null;

        Map<String, Object> beanParams = new HashMap<String, Object>();

        Set<String> keySet = param.keySet();
        for (String key : keySet) {
            Object obj = param.get(key);
            if (obj instanceof List && !key.equals("titleName")) {
                List<Object> objList = (List<Object>) obj;
                if (objList != null && !objList.isEmpty()) {
                    for (int i = 0; i < objList.size(); i = i + templatePageSize) {
                        objects.add(objList.subList(i, i + templatePageSize > objList.size() ? objList.size() : i
                                + templatePageSize));
                    }
                    for (int i = 0; i < objects.size(); i++) {
                        newSheetNames.add("第" + (i + 1) + "页");
                    }
                    beanName = key;
                    continue;
                }
            }
            beanParams.put(key, obj);
        }
        Workbook workbook = null;
        FileInputStream inputStream = new FileInputStream(templateName);
        XLSTransformer transformer = new XLSTransformer();

        logger.info("输入参数beanParams:{}", beanParams);
        logger.info("输入参数newSheetNames:{}", newSheetNames);
        if (newSheetNames.isEmpty()) {
            workbook = transformer.transformXLS(inputStream, beanParams);
        } else {
            workbook = transformer.transformMultipleSheetsList(inputStream, objects, newSheetNames, beanName,
                    beanParams, 0);
        }

        response.setContentType("application/x-msdownload;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + filename);
        workbook.write(response.getOutputStream());
        response.getOutputStream().flush();
        logger.info("=============模板导出结束 =================");
    }

    /**
     * 解决输出文件名的中文乱码问题
     *
     * @param request
     * @param outFileName
     * @return
     * @throws UnsupportedEncodingException
     */
    private static String charEncoding(HttpServletRequest request, String outFileName)
            throws UnsupportedEncodingException {
        String agent = request.getHeader("USER-AGENT").toUpperCase();
        if (!ObjectHelper.isEmpty(agent) && -1 != agent.indexOf("MSIE"))
            outFileName = URLEncoder.encode(outFileName, "UTF-8");
        if (!ObjectHelper.isEmpty(agent) && -1 != agent.indexOf("RV:11"))
            outFileName = URLEncoder.encode(outFileName, "UTF-8");
        else if (!ObjectHelper.isEmpty(outFileName) && -1 != agent.indexOf("MOZILLA"))
            outFileName = new String(outFileName.getBytes(), "ISO8859-1");
            //outFileName = new String(outFileName.getBytes(), "UTF-8");
        else if (!ObjectHelper.isEmpty(agent) && -1 != agent.indexOf("OPERA"))
            outFileName = new String(outFileName.getBytes(), "ISO8859-1");
        return outFileName;
    }

    /**
     * 获取变量值
     *
     * @param mapperStr
     * @param beanName
     * @param beanVal
     * @param isFast
     * @return
     * @throws Exception
     */
    private static String getValueByBeanVal(String mapperStr, String beanName, Object beanVal, boolean isFast) {
        String regex = ".*?\\{(.+?)\\}";
        Pattern pattern = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
        Matcher matchers = pattern.matcher(mapperStr);
        while (matchers.find()) {
            String tmpStr = matchers.group(1);
            mapperStr = mapperStr.replace("{" + tmpStr + "}",
                    isFast ? invokeMethod(tmpStr, beanVal) : invokeMethod(tmpStr, beanName, beanVal));
        }
        return mapperStr;
    }

    /**
     * 执行表达式
     *
     * @param jexlExp
     * @param beanName
     * @param beanVal
     * @return
     */
    private static String invokeMethod(String jexlExp, String beanName, Object beanVal) {
        Object obj;
        try {
            Expression e = jexl.createExpression(jexlExp);
            JexlContext param = new MapContext();
            param.set(beanName, beanVal);
            obj = e.evaluate(param);
            if (null == obj) {
                return "";
            }
        } catch (Exception e) {
            logger.error("执行表达式异常:{}", jexlExp);
            return "";
        }
        return obj.toString();
    }

    /**
     * 执行表达式(此方法更高效)
     *
     * @param jexlExp
     * @param beanVal
     * @return
     */
    private static String invokeMethod(String jexlExp, Object beanVal) {
        String[] jexlExpArr = jexlExp.split("\\.");
        if (jexlExpArr.length == 0) {
            return "";
        }
        if (jexlExpArr.length == 1) {
            return beanVal.toString();
        }
        Object obj = beanVal;
        try {
            for (int i = 1; i < jexlExpArr.length; i++) {
                Method method = obj.getClass().getMethod(jexlExpArr[i].replace("()", ""));
                obj = method.invoke(obj);
                if (obj == null) {
                    return "";
                }
            }
        } catch (Exception e) {
            logger.error("执行表达式异常:{}", jexlExp);
            return "";
        }
        return obj.toString();
    }

    /**
     * 获取导出数据MAP
     *
     * @param mainTitle
     * @param mapper
     * @param beanName
     * @param list
     * @param isFast
     * @return
     * @throws Exception
     */
    private static <T> Map<String, Object> getExportDataMap(String mainTitle, String mapper, String beanName,
                                                            List<T> list, boolean isFast) {
        List<List<Object>> contentList = new ArrayList<List<Object>>();
        List<String> titleName = new ArrayList<String>();
        String[] mapperArr = mapper.split(",");

        for (int i = 0; i < mapperArr.length; i++) {
            titleName.add(mapperArr[i].substring(mapperArr[i].lastIndexOf("=") + 1));
        }

        for (int i = 0; i < list.size(); i++) {
            List<Object> content = new ArrayList<Object>();
            Object beanVal = list.get(i);
            for (int j = 0; j < mapperArr.length; j++) {
                content.add(getValueByBeanVal(mapperArr[j].substring(0, mapperArr[j].lastIndexOf("=")), beanName,
                        beanVal, isFast));
            }
            contentList.add(content);
        }
        Map<String, Object> param = new HashMap<String, Object>();
        param.put("mainTitle", mainTitle);
        param.put("titleName", titleName);
        param.put("contentList", contentList);
        return param;
    }
}
